Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ALTER INDEX

Purpose

Use the `ALTER` `INDEX` statement to change or rebuild an existing index.

See Also: CREATE INDEX for information on creating an index

Prerequisites

The index must be in your own schema or you must have `ALTER` `ANY` `INDEX` system privilege.

To execute the `MONITORING` `USAGE` clause, the index must be in your own schema.

To modify a domain index, you must have `EXECUTE` object privilege on the indextype of the index.

Schema object privileges are granted on the parent index, not on individual index partitions or subpartitions.

You must have tablespace quota to modify, rebuild, or split an index partition or to modify or rebuild an index subpartition.

See Also: CREATE INDEX and Oracle Data Cartridge Developer's Guide for information on domain indexes

Syntax

alter_index::=

Description of the illustration alter_index.gif

(deallocate_unused_clause ::=, allocate_extent_clause ::=, shrink_clause::=, parallel_clause ::=, physical_attributes_clause ::=, logging_clause::=, rebuild_clause ::=, alter_index_partitioning ::=)

deallocate_unused_clause ::=

Description of the illustration deallocate_unused_clause.gif

(size_clause::=)

allocate_extent_clause ::=

Description of the illustration allocate_extent_clause.gif

(size_clause::=)

shrink_clause::=

Description of the illustration shrink_clause.gif

parallel_clause ::=

Description of the illustration parallel_clause.gif

physical_attributes_clause ::=

Description of the illustration physical_attributes_clause.gif

(storage_clause::=)

logging_clause::=

Description of the illustration logging_clause.gif

rebuild_clause ::=

Description of the illustration rebuild_clause.gif

(parallel_clause ::=, physical_attributes_clause ::=, key_compression::=, logging_clause::=)

key_compression::=

Description of the illustration key_compression.gif

alter_index_partitioning ::=

Description of the illustration alter_index_partitioning.gif

(modify_index_default_attrs ::=, add_hash_index_partition ::=, modify_index_partition ::=, rename_index_partition::=, drop_index_partition ::=, split_index_partition::=, coalesce_index_partition ::=, modify_index_subpartition::=)

modify_index_default_attrs ::=

Description of the illustration modify_index_default_attrs.gif

(physical_attributes_clause ::=, logging_clause::=)

add_hash_index_partition ::=

Description of the illustration add_hash_index_partition.gif

(parallel_clause ::=)

coalesce_index_partition ::=

Description of the illustration coalesce_index_partition.gif

(parallel_clause ::=)

modify_index_partition ::=

Description of the illustration modify_index_partition.gif

(deallocate_unused_clause ::=, allocate_extent_clause ::=, physical_attributes_clause ::=, logging_clause::=, key_compression::=)

rename_index_partition::=

Description of the illustration rename_index_partition.gif

drop_index_partition ::=

Description of the illustration drop_index_partition.gif

split_index_partition::=

Description of the illustration split_index_partition.gif

(parallel_clause ::=)

index_partition_description::=

Description of the illustration index_partition_description.gif

(segment_attributes_clause::=, key_compression::=)

segment_attributes_clause::=

Description of the illustration segment_attributes_clause.gif

(physical_attributes_clause ::=, logging_clause::=)

modify_index_subpartition::=

Description of the illustration modify_index_subpartition.gif

(allocate_extent_clause ::=, deallocate_unused_clause ::=)

Semantics

schema

Specify the schema containing the index. If you omit `schema`, Oracle Database assumes the index is in your own schema.

index

Specify the name of the index to be altered.

Restrictions on Modifying Indexes The modification of indexes is subject to the following restrictions:

- If `index` is a domain index, you can specify only the `PARAMETERS` clause, the `RENAME` clause, the `rebuild_clause` (with or without the `PARAMETERS` clause), the `parallel_clause`, or the `UNUSABLE` clause. No other clauses are valid.

- You cannot alter or rename a domain index that is marked `LOADING` or `FAILED`. If an index is marked `FAILED`, the only clause you can specify is `REBUILD`.

See Also: Oracle Data Cartridge Developer's Guide for information on the `LOADING` and `FAILED` states of domain indexes

deallocate_unused_clause

Use the `deallocate_unused_clause` to explicitly deallocate unused space at the end of the index and make the freed space available for other segments in the tablespace.

If `index` is range-partitioned or hash-partitioned, then Oracle Database deallocates unused space from each index partition. If `index` is a local index on a composite-partitioned table, Oracle Database deallocates unused space from each index subpartition.

Restrictions on Deallocating Space Deallocation of space is subject to the following restrictions:

- You cannot specify this clause for an index on a temporary table.

- You cannot specify this clause and also specify the `rebuild_clause`.

Please refer to deallocate_unused_clause for a full description of this clause.

KEEP integer The `KEEP` clause lets you specify the number of bytes above the high water mark that the index will have after deallocation. If the number of remaining extents is less than `MINEXTENTS`, then `MINEXTENTS` is set to the current number of extents. If the initial extent becomes smaller than `INITIAL`, then `INITIAL` is set to the value of the current initial extent. If you omit `KEEP`, all unused space is freed.

Please refer to ALTER TABLE for a complete description of this clause.

allocate_extent_clause

The `allocate_extent_clause` lets you explicitly allocate a new extent for the index. For a local index on a hash-partitioned table, Oracle Database allocates a new extent for each partition of the index.

Restriction on Allocating Extents You cannot specify this clause for an index on a temporary table or for a range-partitioned or composite-partitioned index.

Please refer to allocate_extent_clause for a full description of this clause.

shrink_clause

Use this clause to compact the index segments. Specifying `ALTER` `INDEX` ... `SHRINK` `SPACE` `COMPACT` is equivalent to specifying `ALTER` `INDEX` ... `COALESCE`.

For complete information on this clause, please refer to shrink_clause in the documentation on `CREATE` `TABLE`.

Restriction on Shrinking Index Segments You cannot specify this clause for for a bitmap join index or for a function-based index.

parallel_clause

Use the `PARALLEL` clause to change the default degree of parallelism for queries and DML on the index.

Restriction on Parallelizing Indexes You cannot specify this clause for an index on a temporary table.

For complete information on this clause, please refer to parallel_clause in the documentation on `CREATE` `TABLE`.

See Also: "Enabling Parallel Queries: Example"

physical_attributes_cla use

Use the `physical_attributes_clause` to change the values of parameters for a nonpartitioned index, all partitions and subpartitions of a partitioned index, a specified partition, or all subpartitions of a specified partition.

See Also: the physical attributes parameters in CREATE TABLE "Modifying Real Index Attributes: Example" and "Changing MAXEXTENTS: Example"

Restrictions on Index Physical Attributes Index physical attributes are subject to the following restrictions:

- You cannot specify this clause for an index on a temporary table.

- You cannot specify the `PCTUSED` parameter at all when altering an index.

- You can specify the `PCTFREE` parameter only as part of the `rebuild_clause`, the `modify_index_default_attrs` clause, or the `split_partition_clause`.

storage_clause

Use the `storage_clause` to change the storage parameters for a nonpartitioned index, index partition, or all partitions of a partitioned index, or default values of these parameters for a partitioned index. Please refer to storage_clause for complete information on this clause.

logging_clause

Use the `logging_clause` to change the logging attribute of the index. If you also specify the `REBUILD` clause, then this new setting affects the rebuild operation. If you specify a different value for logging in the `REBUILD` clause, then Oracle Database uses the last logging value specified as the logging attribute of the index and of the rebuild operation.

An index segment can have logging attributes different from those of the base table and different from those of other index segments for the same base table.

Restriction on Index Logging You cannot specify this clause for an index on a temporary table.

See Also: logging_clause for a full description of this clause Oracle Database Concepts and the Oracle Data Warehousing Guide for more information about `LOGGING` and parallel DML

RECOVERABLE | UNRECOVERABLE

These keywords are deprecated and have been replaced with `LOGGING` and `NOLOGGING`, respectively. Although `RECOVERABLE` and `UNRECOVERABLE` are supported for backward compatibility, Oracle strongly recommends that you use the `LOGGING` and `NOLOGGING` keywords.

`RECOVERABLE` is not a valid keyword for creating partitioned tables or LOB storage characteristics. `UNRECOVERABLE` is not a valid keyword for creating partitioned or index-organized tables. Also, it can be specified only with the `AS` subquery clause of `CREATE` `INDEX`.

rebuild_cl au se

Use the `rebuild_clause` to re-create an existing index or one of its partitions or subpartitions. If index is marked `UNUSABLE`, a successful rebuild will mark it `USABLE`. For a function-based index, this clause also enables the index. If the function on which the index is based does not exist, the rebuild statement will fail.

Note: When you rebuild the secondary index of an index-organized table, Oracle Database preserves the primary key columns contained in the logical rowid when the index was created. Therefore, if the index was created with the `COMPATIBLE` initialization parameter set to less than 10.0.0, the rebuilt index will contain the index key and any of the primary key columns of the table that are not also in the index key. If the index was created with the `COMPATIBLE` initialization parameter set to 10.0.0 or greater, then the rebuilt index will contain the index key and all the primary key columns of the table, including those also in the index key.

Restrictions on Rebuilding Indexes The rebuilding of indexes is subject to the following restrictions:

- You cannot rebuild an index on a temporary table.

- You cannot rebuild a bitmap index that is marked `INVALID`. Instead, you must drop and then re-create it.

- You cannot rebuild an entire partitioned index. You must rebuild each partition or subpartition, as described for the `PARTITION` clause.

- You cannot specify the `deallocate_unused_clause` in the same statement as the `rebuild_clause`.

- You cannot change the value of the `PCTFREE` parameter for the index as a whole (`ALTER` `INDEX`) or for a partition (`ALTER` `INDEX` ... `MODIFY` `PARTITION`). You can specify `PCTFREE` in all other forms of the `ALTER` `INDEX` statement.

- For a domain index:

  - You can specify only the `PARAMETERS` clause (either for the index or for a partition of the index) or the `parallel_clause`. No other rebuild clauses are valid.

  - You can rebuild an index only if the index is not marked `IN_PROGRESS`.

  - You can rebuild an index partition only if the index is not marked `IN_PROGRESS` or `FAILED` and the partition is not marked `IN_PROGRESS`.

- You cannot rebuild a local index, but you can rebuild a partition of a local index (`ALTER` `INDEX` ... `REBUILD` `PARTITION`).

- For a local index on a hash partition or subpartition, the only parameter you can specify is `TABLESPACE`.

PARTITION Clause

Use the `PARTITION` clause to rebuild one partition of an index. You can also use this clause to move an index partition to another tablespace or to change a create-time physical attribute.

The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle Database Administrator's Guide for a discussion of these restrictions.

Restriction on Rebuilding Partitions You cannot specify this clause for a local index on a composite-partitioned table. Instead, use the `REBUILD` `SUBPARTITION` clause.

See Also: Oracle Database Administrator's Guide for more information about partition maintenance operations and "Rebuilding Unusable Index Partitions: Example"

SUBPARTITION Clause

Use the `SUBPARTITION` clause to rebuild one subpartition of an index. You can also use this clause to move an index subpartition to another tablespace. If you do not specify `TABLESPACE`, the subpartition is rebuilt in the same tablespace.

The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle Database Administrator's Guide for a discussion of these restrictions.

Restrictions on Modifying Index Subpartitions The modification of index subpartitions is subject to the following restrictions:

- The only parameters you can specify for a subpartition are `TABLESPACE` and the `parallel_clause`.

- You cannot rebuild the subpartition of a list partition.

REVERSE | NOREVERSE

Indicate whether the bytes of the index block are stored in reverse order:

- `REVERSE` stores the bytes of the index block in reverse order and excludes the rowid when the index is rebuilt.

- `NOREVERSE` stores the bytes of the index block without reversing the order when the index is rebuilt. Rebuilding a `REVERSE` index without the `NOREVERSE` keyword produces a rebuilt, reverse-keyed index.

Restrictions on Reverse Indexes Reverse indexes are subject to the following restrictions:

- You cannot reverse a bitmap index or an index-organized table.

- You cannot specify `REVERSE` or `NOREVERSE` for a partition or subpartition.

See Also: "Storing Index Blocks in Reverse Order: Example"

parallel_clause

Use the `parallel_clause` to parallelize the rebuilding of the index.

See Also: "Rebuilding an Index in Parallel: Example"

TABLESPACE Clause

Specify the tablespace where the rebuilt index, index partition, or index subpartition will be stored. The default is the default tablespace where the index or partition resided before you rebuilt it.

key_compression

Specify `COMPRESS` to enable key compression, which eliminates repeated occurrence of key column values. Use `integer` to specify the prefix length (number of prefix columns to compress).

- For unique indexes, the range of valid prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.

- For nonunique indexes, the range of valid prefix length values is from 1 to the number of key columns. The default prefix length is number of key columns.

Oracle Database compresses only nonpartitioned indexes that are nonunique or unique indexes of at least two columns.

Specify `NOCOMPRESS` to disable key compression. This is the default.

Restriction on Key Compression You cannot specify `COMPRESS` for a bitmap index.

ONLINE Clause

Specify `ONLINE` to allow DML operations on the table or partition during rebuilding of the index.

Restrictions on Online Indexes Online indexes are subject to the following restrictions:

- Parallel DML is not supported during online index building. If you specify `ONLINE` and then issue parallel DML statements, Oracle Database returns an error.

- You cannot specify `ONLINE` for a bitmap join index or a cluster index.

- For a nonunique secondary index on an index-organized table, the number of index key columns plus the number of primary key columns that are included in the logical rowid in the index-organized table cannot exceed 32. The logical rowid excludes columns that are part of the index key.

COMPUTE STATISTICS Clause

This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors.

Restriction on COMPUTE STATISTICS Clause You cannot specify this clause for a domain index.

logging_clause

Specify whether the `ALTER` `INDEX` ... `REBUILD` operation will be logged.

Please refer to the logging_clause for a full description of this clause.

PARAMETERS Clause

The `PARAMETERS` clause applies only to domain indexes. This clause specifies the parameter string that is passed uninterpreted to the appropriate ODCI indextype routine. The maximum length of the parameter string is 1000 characters.

If you are altering or rebuilding an entire index, then the string must refer to index-level parameters. If you are rebuilding a partition of the index, then the string must refer to partition-level parameters.

If `index` is marked `UNUSABLE`, then modifying the parameters alone does not make it `USABLE`. You must also rebuild the `UNUSABLE` index to make it usable.

If you have installed Oracle Text, then you can rebuild your Oracle Text domain indexes using parameters specific to that product. For more information on those parameters, please refer to Oracle Text Reference.

Restrictions on the PARAMETERS Clause The `PARAMETERS` clause is subject to the following restrictions:

- You can specify this clause only for a domain index.

- You can modify index partitions only if `index` is not marked `IN_PROGRESS` or `FAILED`, no index partitions are marked `IN_PROGRESS`, and the partition being modified is not marked `FAILED`.

See Also: Oracle Data Cartridge Developer's Guide for more information on indextype routines CREATE INDEX for more information on domain indexes

ENABLE Clause

`ENABLE` applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true:

- The function is currently valid

- The signature of the current function matches the signature of the function when the index was created

- The function is currently marked as `DETERMINISTIC`

Restriction on Enabling Function-based Indexes You cannot specify any other clauses of `ALTER` `INDEX` in the same statement with `ENABLE`.

DISABLE Clause

`DISABLE` applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another `ALTER` `INDEX` statement with the `ENABLE` keyword.

UNUSABLE Clause

Specify `UNUSABLE` to mark the index or index partition(s) or index subpartition(s) `UNUSABLE`. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked `UNUSABLE`, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.

Restriction on Marking Indexes Unusable You cannot specify this clause for an index on a temporary table.

RENAME Clause

Use this clause to rename an index. The `new_index_name` is a single identifier and does not include the schema name.

Restriction on Renaming Indexes For a domain index, neither `index` nor any partitions of `index` can be marked `IN_PROGRESS` or `FAILED`.

See Also: "Renaming an Index: Example"

COALESCE Clause

Specify `COALESCE` to instruct Oracle Database to merge the contents of index blocks where possible to free blocks for reuse.

Restrictions on Coalescing Index Blocks Coalescing of index blocks is subject to the following restrictions:

- You cannot specify this clause for an index on a temporary table.

- Do not specify this clause for the primary key index of an index-organized table. Instead use the `COALESCE` clause of `ALTER` `TABLE`.

See Also: Oracle Database Administrator's Guide for more information on space management and coalescing indexes COALESCE Clause for information on coalescing the space of an index-organized table shrink_clause for an alternative method of compacting index segments

MONITORING USAGE | NOMONITORING USAGE

Use this clause to determine whether Oracle Database should monitor index use.

- Specify `MONITORING` `USAGE` to begin monitoring the index. Oracle Database first clears existing information on index use, and then monitors the index for use until a subsequent `ALTER` `INDEX` ... `NOMONITORING` `USAGE` statement is executed.

- To terminate monitoring of the index, specify `NOMONITORING` `USAGE`.

To see whether the index has been used since this `ALTER` `INDEX` ... `NOMONITORING` `USAGE` statement was issued, query the `USED` column of the `V$OBJECT_USAGE` dynamic performance view.

See Also: Oracle Database Reference for information on the data dictionary and dynamic performance views

UPDATE BLOCK REFERENCES Clause

The `UPDATE` `BLOCK` `REFERENCES` clause is valid only for normal and domain indexes on index-organized tables. Specify this clause to update all the stale guess data block addresses stored as part of the index row with the correct database address for the corresponding block identified by the primary key.

For a domain index, Oracle Database executes the `ODCIIndexAlter` routine with the `alter_option` parameter set to `AlterIndexUpdBlockRefs`. This routine enables the cartridge code to update the stale guess data block addresses in the index.

Restriction on UPDATE BLOCK REFERENCES You cannot combine this clause with any other clause of `ALTER` `INDEX`.

alter_index_partitio ning

The partitioning clauses of the `ALTER` `INDEX` statement are valid only for partitioned indexes.

The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle Database Administrator's Guide for a discussion of these restrictions.

Restrictions on Modifying Index Partitions Modifying index partitions is subject to the following restrictions:

- You cannot specify any of these clauses for an index on a temporary table.

- You can combine several operations on the base index into one `ALTER` `INDEX` statement (except `RENAME` and `REBUILD`), but you cannot combine partition operations with other partition operations or with operations on the base index.

modify_index_default_attrs

Specify new values for the default attributes of a partitioned index.

Restriction on Modifying Partition Default Attributes The only attribute you can specify for a hash-partitioned global index or for an index on a hash-partitioned or composite-partitioned table is `TABLESPACE`.

TABLESPACE Specify the default tablespace for new partitions of an index or subpartitions of an index partition.

logging_clause Specify the default logging attribute of a partitioned index or an index partition.

Please refer to logging_clause for a full description of this clause.

FOR PARTITION Use the `FOR` `PARTITION` clause to specify the default attributes for the subpartitions of a partition of a local index on a composite-partitioned table.

Restriction on FOR PARTITION You cannot specify `FOR` `PARTITION` for a list partition.

See Also: "Modifying Default Attributes: Example"

add_hash_index_partition

Use this clause to add a partition to a global hash-partitioned index. Oracle Database adds hash partitions and populates them with index entries rehashed from an existing hash partition of the index, as determined by the hash function. If you omit the partition name, then Oracle Database assigns a name of the form `SYS_P` `n`. If you omit the `TABLESPACE` clause, then Oracle Database places the partition in the tablespace specified for the index. If no tablespace is specified for the index, then Oracle Database places the partition in the default tablespace of the user, if one has been specified, or in the system default tablespace.

modify_index_partition

Use the `modify_index_partition` clause to modify the real physical attributes, logging attribute, or storage characteristics of index partition `partition` or its subpartitions. For a hash-partitioned global index, the only subclause of this clause you can specify is `UNUSABLE`.

COALESCE Specify this clause to merge the contents of index partition blocks where possible to free blocks for reuse.

UPDATE BLOCK REFERENCES The `UPDATE` `BLOCK` `REFERENCES` clause is valid only for normal indexes on index-organized tables. Use this clause to update all stale guess data block addresses stored in the secondary index partition.

Restrictions on UPDATE BLOCK REFERENCES This clause is subject to the following restrictions:

- You cannot specify the `physical_attributes_clause` for an index on a hash-partitioned table.

- You cannot specify `UPDATE` `BLOCK` `REFERENCES` with any other clause in `ALTER` `INDEX`.

Note: If the index is a local index on a composite-partitioned table, the changes you specify here will override any attributes specified earlier for the subpartitions of index, as well as establish default values of attributes for future subpartitions of that partition. To change the default attributes of the partition without overriding the attributes of subpartitions, use `ALTER` `TABLE` ... `MODIFY` `DEFAULT` `ATTRIBUTES` `OF` `PARTITION`.

See Also: "Marking an Index Unusable: Examples"

UNUSABLE Clause This clause has the same function for index partitions that it has for the index as a whole. Please refer to "UNUSABLE Clause".

key_compression This clause is relevant for composite-partitioned indexes. Use this clause to change the compression attribute for the partition and every subpartition in that partition. Oracle Database marks each index subpartition in the partition `UNUSABLE` and you must then rebuild these subpartitions. Key compression must already have been specified for the table before you can specify key compression for a partition. You can specify this clause only at the partition level. You cannot change the compression attribute for an individual subpartition.

You can use this clause for noncomposite index partitions. However, it is more efficient to use the `rebuild_clause` for noncomposite partitions, which lets you rebuild and set the compression attribute in one step.

rename_index_partition

Use the `rename_index_partition` clauses to rename index `partition` or `subpartition` to `new_name`.

Restrictions on Renaming Index Partitions Renaming index partitions is subject to the following restrictions:

- You cannot rename the subpartition of a list partition.

- For a partition of a domain index, `index` cannot be marked `IN_PROGRESS` or `FAILED`, none of the partitions can be marked `IN_PROGRESS`, and the partition you are renaming cannot be marked `FAILED`.

See Also: "Renaming an Index Partition: Example"

drop_index_partition

Use the `drop_index_partition` clause to remove a partition and the data in it from a partitioned global index. When you drop a partition of a global index, Oracle Database marks the next index partition `UNUSABLE`. You cannot drop the highest partition of a global index.

See Also: "Dropping an Index Partition: Example"

split_index_partition

Use the `split_index_partition` clause to split a partition of a global range-partitioned index into two partitions, adding a new partition to the index. This clause is not valid for hash-partitioned global indexes. Instead, use the `add_hash_index_partition` clause.

Splitting a partition marked `UNUSABLE` results in two partitions, both marked `UNUSABLE`. You must rebuild the partitions before you can use them.

Splitting a usable partition results in two partitions populated with index data. Both new partitions are usable.

AT Clause Specify the new noninclusive upper bound for `split_partition_1`. The `value_list` must evaluate to less than the presplit partition bound for `partition_name_old` and greater than the partition bound for the next lowest partition (if there is one).

INTO Clause Specify (optionally) the name and physical attributes of each of the two partitions resulting from the split.

See Also: "Splitting a Partition: Example"

coalesce_index_partition

This clause is valid only for hash-partitioned global indexes. Oracle Database reduces by one the number of index partitions. Oracle Database selects the partition to coalesce based on the requirements of the hash function. Use this clause if you want to distribute index entries of a selected partition into one of the remaining partitions and then remove the selected partition.

modify_index_subpartition

Use the `modify_index_subpartition` clause to mark `UNUSABLE` or allocate or deallocate storage for a subpartition of a local index on a composite-partitioned table. All other attributes of such a subpartition are inherited from partition-level default attributes.

Examples

Storing Index Blocks in Reverse Order: Example The following statement rebuilds index `ord_customer_ix` (created in "Creating an Index: Example") so that the bytes of the index block are stored in reverse order:

    ALTER INDEX ord_customer_ix REBUILD REVERSE;

Rebuilding an Index in Parallel: Example The following statement causes the index to be rebuilt from the existing index by using parallel execution processes to scan the old and to build the new index:

    ALTER INDEX ord_customer_ix REBUILD PARALLEL;

Modifying Real Index Attributes: Example The following statement alters the `oe.cust_lname_ix` index so that future data blocks within this index use 5 initial transaction entries and an incremental extent of 100 kilobytes:

    /* Unless you change the default tablespace of sample user oe,
       or specify different tablespace storage for the index, this
       example fails because the default tablespace originally assigned
       to oe is locally managed.
    */
    ALTER INDEX oe.cust_lname_ix
        INITRANS 5
        STORAGE (NEXT 100K);

If the `oe.cust_lname_ix` index were partitioned, this statement would also alter the default attributes of future partitions of the index. Partitions added in the future would then use 5 initial transaction entries and an incremental extent of 100K.

Enabling Parallel Queries: Example The following statement sets the parallel attributes for index `upper_ix` (created in "Creating a Function-Based Index: Example") so that scans on the index will be parallelized:

    ALTER INDEX upper_ix PARALLEL;

Renaming an Index: Example The following statement renames an index:

    ALTER INDEX upper_ix RENAME TO upper_name_ix;

Marking an Index Unusable: Examples The following statements use the `cost_ix` index, which was created in "Creating a Range-Partitioned Global Index: Example". Partition `p1` of that index was dropped in "Dropping an Index Partition: Example". The first statement marks index partition `p2` as `UNUSABLE`:

    ALTER INDEX cost_ix
       MODIFY PARTITION p2 UNUSABLE;

The next statement marks the entire index `cost_ix` as `UNUSABLE`:

    ALTER INDEX cost_ix UNUSABLE;

Rebuilding Unusable Index Partitions: Example The following statements rebuild partitions `p2` and `p3` of the `cost_ix` index, making the index once more usable: The rebuilding of partition `p3` will not be logged:

    ALTER INDEX cost_ix
       REBUILD PARTITION p2;
    ALTER INDEX cost_ix
       REBUILD PARTITION p3 NOLOGGING;

Changing MAXEXTENTS: Example The following statement changes the maximum number of extents for partition `p3` and changes the logging attribute:

    /* This example will fail if the tablespace in which partition p3
       resides is locally managed.
    */
    ALTER INDEX cost_ix MODIFY PARTITION p3
       STORAGE(MAXEXTENTS 30) LOGGING;

Renaming an Index Partition: Example The following statement renames an index partition of the `cost_ix` index (created in "Creating a Range-Partitioned Global Index: Example"):

    ALTER INDEX cost_ix
       RENAME PARTITION p3 TO p3_Q3;

Splitting a Partition: Example The following statement splits partition `p2` of index `cost_ix` (created in "Creating a Range-Partitioned Global Index: Example") into `p2a` and `p2b`:

    ALTER INDEX cost_ix
       SPLIT PARTITION p2 AT (1500)
       INTO ( PARTITION p2a TABLESPACE tbs_01 LOGGING,
              PARTITION p2b TABLESPACE tbs_02);

Dropping an Index Partition: Example The following statement drops index partition `p1` from the `cost_ix` index:

    ALTER INDEX cost_ix
       DROP PARTITION p1;

Modifying Default Attributes: Example The following statement alters the default attributes of local partitioned index `prod_idx`, which was created in "Creating an Index on a Hash-Partitioned Table: Example". Partitions added in the future will use 5 initial transaction entries and an incremental extent of 100K:

    ALTER INDEX prod_idx
          MODIFY DEFAULT ATTRIBUTES INITRANS 5 STORAGE (NEXT 100K);